1. Field of the Invention.
This invention relates in general to database management systems performed by computers, and in particular, to the intelligent compilation of materialized view maintenance for query processing in database management systems.
2. Description of Related Art.
Computer systems incorporating Relational DataBase Management System (RDBMS) software using a Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American Nationals Standard Institute (ANSI) and the International Standards Organization (ISO).
For most RDBMS software, combinations of tables and views are used to access data stored in tables in the database. A materialized view or summary table is often based on a xe2x80x9cfull selectxe2x80x9d against one or more underlying base tables, the results of which are then materialized in the view or table. The columns of the view or table are based on the elements of the select list of the full select.
Generally, the query used to generate the summary table or materialized view condenses, combines, or otherwise processes the data residing in one or more underlying base tables. When the underlying base tables are modified, the materialized view or summary table must also be maintained to accurately reflect the modified tables. In general, modification operations made to the same table are synchronized by the DBMS in order that all transactions retrieve the correct results from the materialized view or summary table. However, in the case of summary tables, such synchronization may lead to frequent deadlocks between transactions, since the summary table usually condenses large amounts of data into relatively few rows. In these cases, most modifications to the underlying base tables will need to modify the same set of rows in the summary table.
To alleviate such potential deadlocks, the isolation level or lock modes can be adjusted in such situations where the summary table is being accessed for the purposes of propagating an update. This is often difficult to achieve in the prior art, because it relies upon the skill and knowledge of the person(s) developing the transactions, to ensure certain parts of a transaction run with the minimally correct level of isolation or obtains the minimal required locks in the transactions, rather than upon any automatic operations of the RDBMS software. Furthermore, the RDBMS software typically overrides any such lock modes or isolation levels requested by the user when modifications are involved. Thus, there is a need in the art for automated mechanisms within the RDBMS software that modify transactions to ensure the correct level of isolation.
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for maintaining a materialized view derived from at least one base table in a database stored on a computer. An update is performed to the base table in a transaction. Thereafter, the materialized view is accessed to identify affected records therein using at least one mechanism, such as an isolation level or update- and exclusive-locks, to guarantee consistency while avoiding deadlocks with other transactions that modify at least one base table of the materialized view and to improve concurrency with other transactions that are accessing the materialized view when the modification to the base table is applied to the materialized view.
It is an object of the present invention to optimize queries using materialized views that can be replicated and/or partitioned across multiple processors. More specifically, it is an object of the present invention to the maintenance of materialized views or summary tables in an RDBMS.